package com.chart.controller;

import com.chart.model.StudentEntity;
import com.chart.service.StudentService;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/chart")
public class StatisticChartController {

    @Resource
    private StudentService studentService;

    @RequestMapping(value = "histogram.controller", method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> getHistogram() {
        Map<String, Object> map = new HashMap<>();
        String[] x = new String[]{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"};
        int[] y = new int[]{120, 200, 150, 80, 70, 110, 130};
        map.put("x", x);
        map.put("y", y);
        return map;
    }


    @RequestMapping(value = "/importExcel.controller", produces = "application/json;charset=UTF-8")
    @ResponseBody
    public String importExcel(@RequestParam("myfile") MultipartFile multipartfiles, String navTabId, HttpServletRequest request) {
        //在利用excel插入新的数据时，如果已经配了这个工单的bom，就要先把这工单下的bom给删除了
        try {
            Workbook workbook = getWorkBook(multipartfiles);
            List<String[]> list = new ArrayList<String[]>();
            if (workbook != null) {
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {//判断excle有几个sheet页
                    Sheet sheet = workbook.getSheetAt(sheetNum);//依次读取每一个sheet页
                    if (sheet == null) {//判断当前的sheet页有没有数据
                        continue;//如果没有，则跳过当前的循环，进入下次循环
                    }
                    int firstRowNum = sheet.getFirstRowNum();//读取第一次出现数据的下标（从哪一行开始有数据）
                    int lastRowNum = sheet.getLastRowNum();//读取最后一条数据的下标（没有数据的那一行）
                    for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                        Row row = sheet.getRow(rowNum);//获取当前行的对象
                        if (row == null) {
                            continue;
                        }
                        //年龄
                        if (row.getCell(0) != null) {
                            row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);
                        }
                        Double cell1 = row.getCell(0) == null ? null : row.getCell(0).getNumericCellValue();
                        Integer age = cell1.intValue();


                        //科目
                        if (row.getCell(1) != null) {
                            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        String course = row.getCell(1) == null ? null : row.getCell(1).getStringCellValue();

                        //名字
                        if (row.getCell(2) != null) {
                            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        String name = row.getCell(2) == null ? null : row.getCell(2).getStringCellValue();

                        //成绩
                        if (row.getCell(3) != null) {
                            row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                        }
                        Double cell4 = row.getCell(3) == null ? null : row.getCell(3).getNumericCellValue();
                        Integer score = cell4.intValue();

                        //性别
                        if (row.getCell(4) != null) {
                            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        String sex = row.getCell(4) == null ? null : row.getCell(4).getStringCellValue();

                        //成绩
                        if (row.getCell(5) != null) {
                            row.getCell(5).setCellType(Cell.CELL_TYPE_NUMERIC);
                        }
                        Double cell6 = row.getCell(5) == null ? null : row.getCell(5).getNumericCellValue();
                        Integer totalScore = cell6.intValue();

                        StudentEntity student = new StudentEntity();
                        student.setAge(age);
                        student.setCourse(course);
                        student.setName(name);
                        student.setScore(score);
                        student.setSex(sex);
                        student.setTotalScore(totalScore);
                        student.setUploadFlag(1);
                        studentService.add(student);
                    }
                    //成功
                    return "s";
                }
            }
        } catch (Exception e) {
            //失败
            return "f";
        }
        //无内容
        return "n";
    }

    public static Workbook getWorkBook(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            if (fileName.endsWith("xls")) {
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {

        }
        return workbook;
    }

    public static String stringDateProcess(Cell cell) {
        String result = new String();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil
                    .getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }

        return result;
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = stringDateProcess(cell);
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
